/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.io.ByteArrayOutputStream;
//import java.io.FileInputStream;
//import java.util.logging.Level;
//import java.util.logging.Logger;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.poi.hwpf.HWPFDocumentCore;
import org.apache.poi.hwpf.converter.WordToHtmlConverter;
import org.apache.poi.hwpf.converter.WordToHtmlUtils;
import org.w3c.dom.Document;

import java.io.FileInputStream;
//import java.io.IOException;
//import java.io.InputStream;
//import java.io.StringWriter;
//import javax.xml.transform.OutputKeys;
//import javax.xml.transform.sax.SAXTransformerFactory;
//import javax.xml.transform.sax.TransformerHandler;
//import javax.xml.transform.stream.StreamResult;
//import org.apache.tika.detect.DefaultDetector;
//import org.apache.tika.parser.AutoDetectParser;
//
//import org.apache.tika.parser.ParseContext;
/**
 *
 * @author NamDH
 */
import generate.DetailedLesson.DetailedLesson;
import java.io.StringWriter;
import java.math.BigInteger;
import java.sql.SQLException;

public class DetailedLessonDAO {

    public List getAllDetailedLesson() {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List detailedlessons = new ArrayList();
        try {
            con = connectData.makeConnect();
            String select = "select * from tbl_DetailLesson";
            pstm = con.prepareStatement(select);
            rs = pstm.executeQuery();
            while (rs.next()) {
                DetailedLesson dl = new DetailedLesson();
                dl.setDetailedLessonID(BigInteger.valueOf(rs.getInt("DetailLessonID")));
                dl.setLessonID(BigInteger.valueOf(rs.getInt("LessonID")));
                dl.setPart(rs.getString("Part"));
                dl.setSubstance("Substance");
                detailedlessons.add(dl);
            }
        } catch (Exception e) {
            System.out.print(e);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (pstm != null) {
                    pstm.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
        return detailedlessons;

    }

    public static List<DetailedLesson> getDLessonByID(int lessonId, String detailtype) {
        Connection con = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List detailedlessons = new ArrayList();
        try {
            con = connectData.makeConnect();
            if (detailtype == null) {
                String select = "select * from tbl_DetailLesson where LessonID=?";
                pstm = con.prepareStatement(select);
                pstm.setInt(1, lessonId);
            } else {
                String select = "select * from tbl_DetailLesson where LessonID=? and DetailType=?";
                pstm = con.prepareStatement(select);
                pstm.setInt(1, lessonId);
                pstm.setString(2, detailtype);
            }
            rs = pstm.executeQuery();
            while (rs.next()) {
                DetailedLesson dl = new DetailedLesson();
                dl.setDetailedLessonID(BigInteger.valueOf(rs.getInt("DetailLessonID")));
                dl.setLessonID(BigInteger.valueOf(rs.getInt("LessonID")));
                dl.setPart(rs.getString("Part"));
                String filepath = rs.getString("ContentLocation");
                dl.setSubstance(convertDocToHTML(filepath));
                if (rs.getString("DetailType") != null) {
                    dl.setDetailType(rs.getString("DetailType"));
                }
                detailedlessons.add(dl);
            }
        } catch (Exception e) {
            System.out.print(e);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (pstm != null) {
                    pstm.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
        return detailedlessons;

    }

    public static boolean insertDLesson(int lessonId, String part, String contenLocation, String detailType) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = connectData.makeConnect();
            String sql = "INSERT INTO tbl_DetailLesson(LessonID, Part, ContentLocation, DetailType)"
                    + "VALUES ("+ lessonId +", N'"+ part+ "', N'"+contenLocation+"', "+detailType+")";           
            stm = con.prepareStatement(sql);
            stm.execute();
        } catch (SQLException ex) {
            return false;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return true;
    }

    public static void deleteDLesson(int lessonId, String part) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = connectData.makeConnect();
            String sql = "Delete from tbl_DetailLesson where DetailLessonID = ? and Part = ?";
            stm = con.prepareStatement(sql);
            stm.setInt(1, lessonId);
            stm.setString(2, part);
            stm.execute();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static String convertDocToHTML(String filepath) {
        HWPFDocumentCore wordDocument = null;
        {
            try {         
                wordDocument = WordToHtmlUtils.loadDoc(new FileInputStream(filepath));
                Document newDocument = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
                WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(newDocument);
                wordToHtmlConverter.processDocument(wordDocument);
                StringWriter stringWriter = new StringWriter();
                Transformer transformer = TransformerFactory.newInstance().newTransformer();
                transformer.setOutputProperty(OutputKeys.INDENT, "yes");
                transformer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
                transformer.setOutputProperty(OutputKeys.METHOD, "html");
                transformer.transform(new DOMSource(wordToHtmlConverter.getDocument()), new StreamResult(stringWriter));
                String html = stringWriter.toString();
                return html;
            } catch (IOException ex) {
                Logger.getLogger(DetailedLessonDAO.class.getName()).log(Level.SEVERE, null, ex);
            } catch (TransformerException ex) {
                Logger.getLogger(DetailedLessonDAO.class.getName()).log(Level.SEVERE, null, ex);
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(DetailedLessonDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
}
